<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>MongoDB datastore &#183; GFN InSight documentation</title>
    <meta name="description" content="">
    <meta name="author" content="">

    <!-- Le HTML5 shim, for IE6-8 support of HTML elements -->
    <!--[if lt IE 9]>
      <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->

    <!-- Le styles -->
    <link href="bootstrap.min.css" rel="stylesheet">
    <link href="style.css" rel="stylesheet">
    <style type="text/css">
      /* Override some defaults */
      html, body {
        background-color: #eee;
      }
      body {
        padding-top: 40px; /* 40px to make the container go all the way to the bottom of the topbar */
      }
      .container > footer p {
        text-align: center; /* center align it with the container */
      }
      .container {
        width: 820px; /* downsize our container to make the content feel a bit tighter and more cohesive. NOTE: this removes two full columns from the grid, meaning you only go to 14 columns and not 16. */
      }

      /* The white background content wrapper */
      .container > .content {
        background-color: #fff;
        padding: 20px;
        margin: 0 -20px; /* negative indent the amount of the padding to maintain the grid system */
        -webkit-border-radius: 0 0 6px 6px;
           -moz-border-radius: 0 0 6px 6px;
                border-radius: 0 0 6px 6px;
        -webkit-box-shadow: 0 1px 2px rgba(0,0,0,.15);
           -moz-box-shadow: 0 1px 2px rgba(0,0,0,.15);
                box-shadow: 0 1px 2px rgba(0,0,0,.15);
      }

      /* Page header tweaks */
      .page-header {
        background-color: #f5f5f5;
        padding: 20px 20px 10px;
        margin: -20px -20px 20px;
      }

      /* Styles you shouldn't keep as they are for displaying this base example only */
      .content .span10,
      .content .span4 {
        min-height: 500px;
      }
      /* Give a quick and non-cross-browser friendly divider */
      .content .span4 {
        margin-left: 0;
        padding-left: 19px;
        border-left: 1px solid #eee;
      }

      .topbar .btn {
        border: 0;
      }

    </style>

    <!-- Le fav and touch icons -->
    <link rel="shortcut icon" href="images/favicon.ico">
    <link rel="apple-touch-icon" href="images/apple-touch-icon.png">
    <link rel="apple-touch-icon" sizes="72x72" href="images/apple-touch-icon-72x72.png">
    <link rel="apple-touch-icon" sizes="114x114" href="images/apple-touch-icon-114x114.png">
  </head>

  <body>

<div class="topbar">
      <div class="fill">
        <div class="container">
          <a class="brand" href="http://stormy-stone-6022.heroku.com">GFN InSight</a>
          <ul class="nav">
	    <li><a href="index.html">Home</a></li>
            <li class="active"><a href="#">MongoDB datastore</a></li>
            <li><a href="flashapp.html">Flash app</a></li>
            <li><a href="roradmin.html">RoR Admin</a></li>
          </ul>
        </div>
      </div>
    </div>


    <div class="container">

      <div class="content">
        <div class="page-header">
          <h1>MongoDB datastore <small>API as a data source for Flash app hosted on MongoLabs</small></h1>
        </div>
        <div class="row">
          <div class="span10">
            <h2>Overview</h2>
            MongoDB has been used as the database solution for this project because of the following reasons:
            <ul>
            	<li><strong>Scalable</strong>: With support for features like map/reduce, MongoDB has been designed to be a non-relational database solution for storing
            		key/value pairs that are required to be accessed by distributed systems. With the nature of data used by GFN being primarily name/value pairs, this serves as an optimal solution
            		to support the massive number of records used in calculating footprint statistics.</li>
            	<li><strong>High performance</strong>: Compared to tradition SQL database solutions, a noSQL solution like MongoDB has a very high performance due to reduced overhead and large data indexing.</li>
            	<li><strong>Application-friendly</strong>: MongoDB outputs its data to a browser or any HTTP service in JSON, which can be used by web-applications anywhere without the need of any SQL drivers. This eliminates the need for defining an application API stack on top of the data store for most obvious applications of this data.</li>
            </ul><p/>
            To learn more about the advantages of using MongoDB, visit <a href="http://www.mongodb.org/">http://www.mongodb.org/</a>.<p/>
          	<h2>Tables (collections) and schemas</h2>
          	<h3>Structure and architecture</h3>
          	In MongoDB, an equivalent of a SQL table is known as a collection. In order to store all the data from the MS Excel spreadsheets in a scalable way, we have designed the architecture such that given there are <em>n</em> countries to be stored in the database, there are going to be <em>5n+1</em> collections in all.<p/>
          	While the one fixed collection is <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/Countries?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">Countries</span></a>, the 5 collections for each country are: <span class="label warning no-transform">NFA_&lt;countryID&gt;</span>, <span class="label warning no-transform">EF_&lt;countryID&gt;</span>, <span class="label warning no-transform">CoLUM_&lt;countryID&gt;</span>, <span class="label warning no-transform">FD_&lt;countryID&gt;</span> & <span class="label warning no-transform">EF_&lt;countryID&gt;</span>.
          	The <em>&lt;countryID&gt;</em> is a unique identifier for each country, based on the <a href="http://www.iso.org/iso/country_codes/iso_3166_code_lists.htm" target="_blank"> 3166-1 alpha-3 specification</a>. These identifiers have already been incorported into the <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/Countries?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">Countries</span></a> collection. 
          	
          	<h3>Collection schemas</h3>
          	Here are the schemas for the 5 collections for each country, as identified above:<p/>
          	<br/><span class="label success no-transform bigger">Countries</span><p/>
          	    <table class="zebra-striped">
    				<tr>
    					<th>#</th>
    					<th>Field</th>
    					<th>Description</th>
    					<th>Data type</th>
    				</tr>
    				<tr>
    					<td>1</td>
    					<td><strong>id</strong></td>
    					<td>Index of the collection (Primary key)</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>2</td>
    					<td><strong>name</strong></td>
    					<td>The name of the country</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>3</td>
    					<td><strong>biography</strong></td>
    					<td>Basic information about the country to be used as summary data</td>
    					<td><em>longtext</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>identifier</strong></td>
    					<td>The <a href="http://www.iso.org/iso/country_codes/iso_3166_code_lists.htm" target="_blank"> 3166-1 alpha-3 specification</a> defined identifier for the country</td>
    					<td><em>string</em></td>
    				</tr>
    			</table><p/>
          	<br/><span class="label warning no-transform bigger">EI_&lt;countryID&gt;</span> <small class="not-that-small">Exports and Imports data</small><p/>
          	   <table class="zebra-striped">
    				<tr>
    					<th>#</th>
    					<th>Field</th>
    					<th>Description</th>
    					<th>Data type</th>
    				</tr>
    				<tr>
    					<td>1</td>
    					<td><strong>id</strong></td>
    					<td>Index of the collection (Primary key)</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>2</td>
    					<td><strong>year</strong></td>
    					<td>The year the data is from</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>3</td>
    					<td><strong>country_id</strong></td>
    					<td>The id of the other country involved in the trade (Foreign Key)</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>land_type</strong></td>
    					<td>The land type the data concerns, such as Cropland or Fisheries</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>trade_type</strong></td>
    					<td>A toggle value identifying the info as Import or Export</td>
    					<td><em>boolean</em></td>
    				</tr>
    				    				<tr>
    					<td>4</td>
    					<td><strong>amount</strong></td>
    					<td>The amount of the resource being traded</td>
    					<td><em>int</em></td>
    				</tr>
    			</table><p/>
    			<br/><span class="label warning no-transform bigger">FD_&lt;countryID&gt;</span> <small class="not-that-small">Final demand data</small><p/>
          	   <table class="zebra-striped">
    				<tr>
    					<th>#</th>
    					<th>Field</th>
    					<th>Description</th>
    					<th>Data type</th>
    				</tr>
    				<tr>
    					<td>1</td>
    					<td><strong>id</strong></td>
    					<td>Index of the collection (Primary key)</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>2</td>
    					<td><strong>year</strong></td>
    					<td>The year the data is from</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>3</td>
    					<td><strong>category</strong></td>
    					<td>Toggle for if the information is HH, GOV, or GFCF</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>item</strong></td>
    					<td>The specific item from the spreadsheet the data refers to</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>amount</strong></td>
    					<td>The amount of the resource being demanded</td>
    					<td><em>int</em></td>
    				</tr>
    			</table><p/>
    			<br/><span class="label warning no-transform bigger">CoLUM_&lt;countryID&gt;</span> <small class="not-that-small">Consumption Land Use Matrix data</small><p/>
          	    <table class="zebra-striped">
    				<tr>
    					<th>#</th>
    					<th>Field</th>
    					<th>Description</th>
    					<th>Data type</th>
    				</tr>
    				<tr>
    					<td>1</td>
    					<td><strong>id</strong></td>
    					<td>Index of the collection (Primary key)</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>2</td>
    					<td><strong>year</strong></td>
    					<td>The year the data is from</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>3</td>
    					<td><strong>land_type</strong></td>
    					<td>The land type the data concerns, such as Cropland or Fisheries</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>category</strong></td>
    					<td>Toggle for if the information is HH, GOV, or GFCF</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>item</strong></td>
    					<td>The specific item from the spreadsheet the data refers to</td>
    					<td><em>double</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>amount</strong></td>
    					<td>The amount of the resource being consumed</td>
    					<td><em>int</em></td>
    				</tr>
    			</table><p/>
    		<br/><span class="label warning no-transform bigger">EF_&lt;countryID&gt;</span> <small class="not-that-small">Ecological Footprint data</small><p/>
          	    <table class="zebra-striped">
    				<tr>
    					<th>#</th>
    					<th>Field</th>
    					<th>Description</th>
    					<th>Data type</th>
    				</tr>
    				<tr>
    					<td>1</td>
    					<td><strong>id</strong></td>
    					<td>Index of the collection (Primary key)</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>2</td>
    					<td><strong>year</strong></td>
    					<td>The year the data is from</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>3</td>
    					<td><strong>land_type</strong></td>
    					<td>The land type the data concerns, such as Cropland or Fisheries</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>name</strong></td>
    					<td>The specific area of the resource according to the spreadsheet</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>EF_type</strong></td>
    					<td>A toggle for if the information is Import, Export, Production, Consumption, or Biocapacity</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>amount</strong></td>
    					<td>The amount of the resource being demanded/consumed</td>
    					<td><em>int</em></td>
    				</tr>
    			</table><p/>
    		<br/><span class="label warning no-transform bigger">NFA_&lt;countryID&gt;</span> <small class="not-that-small">National Footprint Account data</small><p/>
          	    <table class="zebra-striped">
    				<tr>
    					<th>#</th>
    					<th>Field</th>
    					<th>Description</th>
    					<th>Data type</th>
    				</tr>
    				<tr>
    					<td>1</td>
    					<td><strong>id</strong></td>
    					<td>Index of the collection (Primary key)</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>2</td>
    					<td><strong>year</strong></td>
    					<td>The year the data is from</td>
    					<td><em>int</em></td>
    				</tr>
    				<tr>
    					<td>3</td>
    					<td><strong>per_capita</strong></td>
    					<td>A toggle for if the data is per capita or totals</td>
    					<td><em>boolean</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>EF_type</strong></td>
    					<td>A toggle for if the information is Import, Export, Production, Consumption, or Biocapacity</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>land_type</strong></td>
    					<td>The land type the data concerns, such as Cropland or Fisheries</td>
    					<td><em>string</em></td>
    				</tr>
    				<tr>
    					<td>4</td>
    					<td><strong>amount</strong></td>
    					<td>The amount of the resource being demanded/consumed</td>
    					<td><em>int</em></td>
    				</tr>
    			</table><p/>
          	<h2>From MS Excel spreadsheets to the datastore</h2>
          	
          	These data schemas serve as the models for the database. These have been derived by
          	analyzing the data in the MS Excel sheets, and then constructing an architecture around the data. That results in
          	pretty consistent schemas, allowing the possibility to manually move all the data in the spreadsheets into the datastore.<p/>
          	Described below is the two-step process to be done for each country:
          	
          	<h3>Part I: Mapping data from MS Excel spreadsheets</h3>
          	First, the data needs to be extracted from the MS Excel sheets manually. Here are the steps for the same:
          	<ol>
          		<li>Create a new office spreadsheet, in any Office application (preferrably <a href="http://www.libreoffice.org/">LibreOffice</a> or MS Office), with multiple worksheets (typically 5, if you intend to bootstrap the datastore with a particular country's data)</li>
          		<li>Using one schema of a table for one worksheet, list the fields as of the schema as the first row of the worksheet</li>
          		<li>Carefully pay attention to the schema definition of each field, and learn how the definitions describe <a href="#compacting">compacting the data</a></li>
          		<li>For each record, manually fill the data that corresponds to A SINGLE UNIQUE CELL of data in the MS Excel spreadsheet, and continue to do this for all cells in the worksheet (including the totals at the end of the columns and rows). After you have manually established some repeating patterns of data, you should be able to use the application's autocomplete functionalities to prepare a scaffold for the remaining parts of the sheet.
          			When trying to copy these values from the spreadsheets, you will run into the 
          			challenge of breaking the cells after being copied, because of them being composed of formulae. In order to avoid this problem, ensure that while pasting, you use "Paste special..." and remove "Formula" from your paste type every time you do this.</li>
          		<li>By doing very simple math, you should be able to estimate the number of records you are required to have in each worksheet (based on the rows and columns you are capturing). Double check this with your worksheet, and correct for inconsistencies.</li>
          		<li>After completing each of the 5 worksheet for every single country, you are required to export this data to <strong>.csv</strong> files to make it import-ready for the MongoDB instance you are working with. Your office application should have a built-in functionality to write these worksheets individually to separate .csv's.</li>
          	</ol>
          	An example of the same (FD_CHN) is available <a href="FD_CoLUM_CHN.xls">here</a> for you to be able to refer to the final expected output.
          	<h3>Part II: Importing data into the MongoDB datastore</h3>
          	This is the simplest step in the process. Once you have 5 separate CSV files for each country that you wish import into the datastore, you are ready to use command line tools to import them. You can import each of these individual files using the following command from the terminal:
          	<p/><code>$ mongoimport -d test -c FD_OMN FD_OMN.csv</code><p/>
          	where <em>test</em> is the name of the database to use, FD_OMN is the name of the collection that is meant to store data on the final demand of Oman, and FD_OMN.csv is the one file among the resulting files you obtained in part I. If this process results in errors the first few times, go back to your data and ensure it is consistent. Also, check for the presence of your database. Lastly, if nothing works, find and open your MongoDB log to get a stack trace of the error causing the failure.<p/>
          	For more information on this process, visit <a href="http://www.mongodb.org/display/DOCS/Import+Export+Tools" target="_blank">MongoDB's documentation on importing and exporting</a>.
          	<h3>Local MongoDB instance to MongoLabs MongoDB instance <small>Optional</small></h3>
          	At all times, it is suggested that you run these experiments of preparing data and running the service to observe the JSON response on your personal computer or development server first, and then use MongoLabs tools to push ("restore") the collections onto the web datastore. In order to the same, you need to first create a dump of the current collections using the following command (for each collection):
          	<p/><code>$ ./mongodump --db test --collection FD_OMN</code><p/>
          	or the following for the entire database:
          	<p/><code>$ ./mongodump</code><p/>
          	where <em>test</em> is the name of the database, and <em>FD_OMN</em>is the specific collection you are dumping.<p/>
          	Yet again, for more information on this process, visit <a href="http://www.mongodb.org/display/DOCS/Import+Export+Tools" target="_blank">MongoDB's documentation on importing and exporting</a>.<p/>
          	You are going to get dump files of <strong>.bson</strong> type. Now it is time to push these files to the web datastore. The way to do is well documented on the <a href="http://devcenter.heroku.com/articles/mongolab#connecting_your_heroku_application_to_your_mongodb_database" target="_blank">MongoLabs FAQs on the Heroku document DevCenter</a> or more easily when logged into Heroku as the owner of the application, on the MongoLabs page. There is only one simple command to be run in order to accomplish this:
          	<p/><code>$ mongorestore -h &lt;new host&gt;:&lt;new port&gt; -d heroku_app1234 -u heroku_app1234 -p &lt;new_password&gt; dump-dir/*</code>
          	<div class="alert-message warning">Please be very cautious when using this command - using it twice with the same parameters may cause the collections to be populated with duplicate copies of the same records, rather than overwriting the entire collection.</div>
          	<p/>Once pushed to the datastore, you will be able to see the resulting database's JSON output from the cloud. <p/>An example of the same is here: <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/CoLUM_CHN?apiKey=4ea72412e4b0cb78171ca376">https://mongolab.com/api/1/databases/heroku_app1595140/collections/CoLUM_CHN?apiKey=4ea72412e4b0cb78171ca376</a>
          	<a name="compacting"></a><h2>Compacting the datastore</h2>
          	In order to ensure high-efficiency from the very beginning, the database schema fields have been designed such that there is maximum efficiency, and only the minimally needed data is transferred. For example, collections <span class="label warning no-transform">FD_&lt;countryID&gt;</span> and <span class="label warning no-transform">CoLUM_&lt;countryID&gt;</span> both use a field <em>category</em>, which stores one of the following 3 values: <strong>HH</strong> for Households, <strong>GOV</strong> for Governments, and <strong>GFCF</strong> for Gross Fixed Capital Formation.
          	<p/>Another example is the use of this concept for the <em>item</em> field in the <span class="label warning no-transform">CoLUM_&lt;countryID&gt;</span> collection. Rather than storing the actual value of the name of the item, only the item number is stored, because this helps us keep the database small and consequently asynchronous requests small. This is done as our database knows the consistent listing of items for every <span class="label warning no-transform">CoLUM_&lt;countryID&gt;</span> table for all countries, and thus implements the mapped listing to these numbers and short-codes in the <a href="flashapp.html">model of the application</a>.
          	<h2>External documentation</h2>
          	All the information above is specific to the GFN application, but there is plenty of external documentation on getting started with the MongoDB technology, online. Here are some of the resources:
          	<ul>
          		<li><a href="http://www.mongodb.org/" target="_blank">Official MongoDB site</a></li>
          		<li><a href="http://speakerdeck.com/u/jnunemaker/p/why-mongodb-is-awesome" target="_blank">Why MongoDB is awesome</a></li>
          		<li><a href="http://www.mongodb.org/display/DOCS/Overview+-+The+MongoDB+Interactive+Shell#Overview-TheMongoDBInteractiveShell-Updating" target="_blank">MongoDB Shell commands</a></li>
          		<li><a href="http://www.mongodb.org/display/DOCS/Tutorial" target="_blank">MongoDB Tutorial</a></li>
          	</ul>
          </div>
          <div class="span4">
          	<h3>Current status</h3>
          	Currently, only the data from China's accounts from 2007 have been stored in the database. The database thus included the collections <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/NFA_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">NFA_CHN</span></a>, <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/EF_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">EF_CHN</span></a>, <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/CoLUM_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">CoLUM_CHN</span></a>, <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/FD_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">FD_CHN</span></a> & <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/IE_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">IE_CHN</span></a>. These, along with a <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/Countries?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">Countries</span></a> collection lie in the datastore.<p/>
          	<h3>Open challenges</h3>
          	<ul>
          		<li>In order to import GFN's current .csv file on the NFA accounts from 1961 from all the countries, the schema of NFA in database needs to be re-thought and implemented. Currently, the database stores NFAs of every country separately for all the years.<p/></li>
          		<li>While this document claims that there is perfect consistency among current data, and these comply fully to our described schema, there are few places where we have erred. For example, despite both the collections having standard <em>item</em> listings, we have used a key in <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/CoLUM_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">CoLUM_CHN</span></a> for this field, we have used the exact string value in <a href="https://mongolab.com/api/1/databases/heroku_app1595140/collections/FD_CHN?apiKey=4ea72412e4b0cb78171ca376" target="_blank"><span class="label success no-transform">FD_CHN</span></a></li>
            </ul>
            <h3>Software versions</h3>
          	Here is the list of the current software/driver versions in use, with no conclusions or suggestions on which versions work better than the others:
          	<ul>
          		<li>MongoDB: 1.6.3</li>
          	</ul>
            <h3>Additional notes</h3>
		In order to access this data collection store, you need to be an owner/collaborator of the project stormy-stone-6022 on Heroku. If you are not the current owner, kindly discuss this with the administrator of this application, Kyle Gracey.
          </div>
        </div>
      </div>

      <footer>
        <p>&copy; Team GFN InSight &#183; Department of Information Systems, Carnegie Mellon University</p>
      </footer>

    </div> <!-- /container -->

  </body>
</html>
